package com.sohu.peak.gallery.dao.hibernate;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Example;

import com.sohu.peak.gallery.dao.CateClickStatDAO;
import com.sohu.peak.gallery.domain.CateClickStat;
import com.sohu.peak.gallery.domain.Category;
import com.sohu.peak.util.HibernateHelper;
import com.sohu.peak.util.Logger;
import com.sohu.peak.util.Pagination;


public class CateClickStatHibernatetDAO extends BaseHibernateDAO implements CateClickStatDAO {
	private static final Logger log = new Logger();
	// property constants
	public static final String CATE_ID = "cateId";
	public static final String CLICK_COUNT = "clickCount";
	public static final String CATE_NAME = "cateName";

	public void save(CateClickStat transientInstance) {
		log.debug("saving CateClickStat instance");
		try {
			getSession().save(transientInstance);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}

	public void delete(CateClickStat persistentInstance) {
		log.debug("deleting CateClickStat instance");
		try {
			getSession().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}

	public CateClickStat findById(java.lang.Integer id) {
		log.debug("getting CateClickStat instance with id: " + id);
		try {
			CateClickStat instance = (CateClickStat) getSession().get(
					"com.sohu.peak.gallery.domain.CateClickStat", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}

	public List findByExample(CateClickStat instance) {
		log.debug("finding CateClickStat instance by example");
		try {
			List results = getSession().createCriteria(
					"com.sohu.peak.gallery.domain.CateClickStat").add(
					Example.create(instance)).list();
			log.debug("find by example successful, result size: "
					+ results.size());
			return results;
		} catch (RuntimeException re) {
			log.error("find by example failed", re);
			throw re;
		}
	}

	public List findByProperty(String propertyName, Object value) {
		log.debug("finding CateClickStat instance with property: "
				+ propertyName + ", value: " + value);
		try {
			String queryString = "from CateClickStat as model where model."
					+ propertyName + "= ?";
			Query queryObject = getSession().createQuery(queryString);
			queryObject.setParameter(0, value);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;
		}
	}

	public List findByCateId(Object cateId) {
		return findByProperty(CATE_ID, cateId);
	}

	public List findByClickCount(Object clickCount) {
		return findByProperty(CLICK_COUNT, clickCount);
	}

	public List findByCateName(Object cateName) {
		return findByProperty(CATE_NAME, cateName);
	}

	public List findAll() {
		log.debug("finding all CateClickStat instances");
		try {
			String queryString = "from CateClickStat";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	public CateClickStat merge(CateClickStat detachedInstance) {
		log.debug("merging CateClickStat instance");
		try {
			CateClickStat result = (CateClickStat) getSession().merge(
					detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}

	public void attachDirty(CateClickStat instance) {
		log.debug("attaching dirty CateClickStat instance");
		try {
			getSession().saveOrUpdate(instance);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	public void attachClean(CateClickStat instance) {
		log.debug("attaching clean CateClickStat instance");
		try {
			getSession().lock(instance, LockMode.NONE);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	@SuppressWarnings("unchecked")
	public List<CateClickStat> getCateClickStatListByCateId(Long cid,Pagination pagination) {
		Date s_time=new Date();
		Calendar ca = Calendar.getInstance();
		ca.setTime(s_time);
		int year = ca.get(Calendar.YEAR);
		int month = ca.get(Calendar.MONTH);
		int day = ca.get(Calendar.DAY_OF_MONTH);
		day = day - 1;
		ca.set(year, month, day, 0, 0, 0);
		s_time = ca.getTime();
		
		return getCateClickStatListByCateIdAndDate(cid,s_time,pagination) ;
	}

	@SuppressWarnings("unchecked")
	public List<CateClickStat> getCateClickStatListByCateIdAndDate(Long cid,
			Date s_time, Pagination pagination) {
		ArrayList paramsList = new ArrayList();
		Session session = getSession();
		String queryStr = "select id from Category ";
		queryStr += "where 1=1 ";

		queryStr += "and INSTR(cateRela,?)>0 ";
		Query queryObject = getSession().createQuery(queryStr);
		queryObject.setParameter(0, "|" + cid + "|");
		List result = queryObject.list();
		
		queryStr = "from CateClickStat cs where (cs.cateId=? ";
		paramsList.add(cid.intValue());
		
		
		if (result != null && result.size() > 0) {

			queryStr += "or cs.cateId in ( ";
			for (int i = 0; i < result.size(); i++) {
				if (i != result.size() - 1)
					queryStr += result.get(i) + ",";
				else
					queryStr += result.get(i) + ") ";
			}
		}
		queryStr+=") ";
		
		if(s_time!=null){
			queryStr+="and cs.time>=? ";
			paramsList.add(s_time);
			Calendar ca = Calendar.getInstance();
			ca.setTime(s_time);
			int year = ca.get(Calendar.YEAR);
			int month = ca.get(Calendar.MONTH);
			int day = ca.get(Calendar.DAY_OF_MONTH);
			day = day + 1;
			ca.set(year, month, day, 0, 0, 0);
			s_time = ca.getTime();
			queryStr+="and cs.time<? ";
			paramsList.add(s_time);
		}
		
		queryStr+="order by cs.clickCount desc ";
		List<CateClickStat> list = (List<CateClickStat>) HibernateHelper.pageQuery(
				session, session.createQuery(queryStr), pagination, paramsList
						.toArray());
		if (list.size() == 0)
			return null;
		return list;
	}
	
	public List<CateClickStat> getCateClickStatListByCateIdFromBeingToEnd(Long cid,
			Date b_time,Date e_time ,Pagination pagination) {
		
		
		ArrayList paramsList = new ArrayList();
		Session session = getSession();
		String queryStr = "select id from Category ";
		queryStr += "where 1=1 ";

		queryStr += "and INSTR(cateRela,?)>0 ";
		Query queryObject = getSession().createQuery(queryStr);
		queryObject.setParameter(0, "|" + cid + "|");
		List result = queryObject.list();
		
		queryStr = "select  cs.id,cs.category,sum(cs.clickCount) AS counts,cs.time,cs.cateName from CateClickStat cs where (cs.category.id=? ";
		paramsList.add(cid);
		
		
		if (result != null && result.size() > 0) {

			queryStr += "or cs.category.id in ( ";
			for (int i = 0; i < result.size(); i++) {
				if (i != result.size() - 1)
					queryStr += result.get(i) + ",";
				else
					queryStr += result.get(i) + ") ";
			}
		}
		queryStr+=") ";
		
		if(b_time!=null){
			queryStr+="and cs.time>=? ";
			paramsList.add(b_time);
			queryStr+="and cs.time<=? ";
			paramsList.add(e_time);
		}
		
		
		queryStr+=" group by cs.category  order by sum(cs.clickCount) desc";
		Collection collect = HibernateHelper.pageQuery(
				session, session.createQuery(queryStr), null, paramsList
						.toArray());

		List<CateClickStat> list=new ArrayList<CateClickStat>();
		for (Iterator it = collect.iterator(); it.hasNext(); ) {

            Object[] object = (Object[]) it.next();
            CateClickStat cs=new CateClickStat();
            cs.setId(new Integer(object[0].toString()));
            cs.setCategory((Category)object[1]);
            cs.setClickCount((new Integer(object[2].toString())));
            cs.setCateName(new String(object[4].toString()));
           // log.debug(cs.getCateName());
            list.add(cs);

		}
		return list;
	}

	public List<CateClickStat> getFirstLevelCslFByCateId(Long cid) {
		ArrayList paramsList = new ArrayList();
		Date s_time=new Date();
		Calendar ca = Calendar.getInstance();
		ca.setTime(s_time);
		int year = ca.get(Calendar.YEAR);
		int month = ca.get(Calendar.MONTH);
		int day = ca.get(Calendar.DAY_OF_MONTH);
		day = day - 1;
		ca.set(year, month, day, 0, 0, 0);
		s_time = ca.getTime();
		Session session = getSession();
		String queryStr = "from CateClickStat cs where cs.category.id in( select id from Category where cateType<>2 and cateRela like '%|" + cid + "|') ";
		queryStr+="and cs.time>=? ";
		paramsList.add(s_time);
		
		day = day + 1;
		ca.set(year, month, day, 0, 0, 0);
		s_time = ca.getTime();
		queryStr+="and cs.time<? ";
		paramsList.add(s_time);
		
		queryStr+="order by cs.clickCount desc";
		
		List<CateClickStat> list = (List<CateClickStat>) HibernateHelper.pageQuery(
				session, session.createQuery(queryStr), null, paramsList
						.toArray());
		if (list.size() == 0)
			return null;
		return list;
	}

	public List<CateClickStat> getFirstLevelCslListByCateIdAndDate(Long cid,
			Date s_time, Pagination pagination) {
		ArrayList paramsList = new ArrayList();
		Calendar ca = Calendar.getInstance();
		ca.setTime(s_time);
		int year = ca.get(Calendar.YEAR);
		int month = ca.get(Calendar.MONTH);
		int day = ca.get(Calendar.DAY_OF_MONTH);
		day = day - 1;
		ca.set(year, month, day, 0, 0, 0);
		s_time = ca.getTime();
		Session session = getSession();
		String queryStr = "from CateClickStat cs where cs.category.id in( select id from Category where cateType<>2 and cateRela like '%|" + cid + "|') ";
		queryStr+="and cs.time>=? ";
		paramsList.add(s_time);
		day = day + 1;
		ca.set(year, month, day, 0, 0, 0);
		s_time = ca.getTime();
		queryStr+="and cs.time<? ";
		paramsList.add(s_time);
		
		queryStr+="order by cs.clickCount desc ";
		List<CateClickStat> list = (List<CateClickStat>) HibernateHelper.pageQuery(
				session, session.createQuery(queryStr), pagination, paramsList
						.toArray());
		if (list.size() == 0)
			return null;
		return list;
	}

	public List<CateClickStat> getFirstLevelCslListByCateIdFromBeingToEnd(
			Long cid, Date b_time, Date e_time, Pagination pagination) {
		ArrayList paramsList = new ArrayList();
		Session session = getSession();
		
		String queryStr = "select id from Category ";
		queryStr += "where  cateRela like '%|" + cid + "|')" ;
		Query queryObject = getSession().createQuery(queryStr);
		List result = queryObject.list();

		
	    queryStr = "select  cs.id,cs.category,sum(cs.clickCount) AS counts,cs.time,cs.cateName from CateClickStat cs where";
		
		
		if (result != null && result.size() > 0) {

			queryStr += " cs.category.id in ( ";
			for (int i = 0; i < result.size(); i++) {
				if (i != result.size() - 1)
					queryStr += result.get(i) + ",";
				else
					queryStr += result.get(i) + ") ";
			}
		}

		
		queryStr+="and cs.time>=? ";
		paramsList.add(b_time);
		
		queryStr+="and cs.time<? ";
		paramsList.add(e_time);
		
		
		queryStr+=" group by cs.category  order by sum(cs.clickCount) desc";

		Collection collect = HibernateHelper.pageQuery(
				session, session.createQuery(queryStr), null, paramsList
						.toArray());

		List<CateClickStat> list=new ArrayList<CateClickStat>();
		for (Iterator it = collect.iterator(); it.hasNext(); ) {

            Object[] object = (Object[]) it.next();
            CateClickStat cs=new CateClickStat();
            cs.setId(new Integer(object[0].toString()));
            cs.setCategory((Category)object[1]);
            cs.setClickCount((new Integer(object[2].toString())));
            cs.setCateName(new String(object[4].toString()));
           // log.debug(cs.getCateName());
            list.add(cs);

		}
		return list;
	}
}